/****** Object:  View [dbo].[AuthZ_vwUserRole]    Script Date: 06/24/2009 23:34:27 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_vwUserRole]'))
DROP VIEW [dbo].[AuthZ_vwUserRole]
GO

/****** Object:  View [dbo].[AuthZ_vwUserRole]    Script Date: 06/24/2009 23:34:27 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <05/13/2009>
-- Update date: <6-11-2009>
-- Description:	<Consolidates User information from User, Person and Role tables.>
-- Param:
-- =============================================
CREATE VIEW [dbo].[AuthZ_vwUserRole] AS

SELECT DISTINCT
  p.PersonID
, p.PersonnelNumber
, p.FullName
, p.EMailName
, au.DisplayName
, au.sAMAccountName AS 'Alias'
, r.RoleName
, r.RoleID
FROM [dbo].[AuthZUser] AS au
JOIN [dbo].[AuthZUserRoleMapping] AS arm
ON   arm.AuthZUserID = au.AuthZUserID
JOIN [dbo].[Role] AS r
ON   r.RoleID = arm.RoleID
JOIN [dbo].[Person] p
ON   p.PersonnelNumber = au.PersonnelNumber

GO


